'use strict';

const authConst = require('../constant/auth-const');
const dataConst = require('../constant/data-const');
const Service = require('egg').Service;

class MemberRelationService extends Service {
    async getMemberRelationSrv(userId, targetUserId) {
        const { app } = this;

        return await app.model.MemberRelation.findOne({
            where: {
                user_id: userId,
                target_user_id: targetUserId
            },
            attributes: ['id', 'type']
        });
    }

    async addMemberRelationSrv(userId, targetUserId, relationType) {
        const { app } = this;

        const result = await app.model.MemberRelation.findOne({
            where: {
                user_id: userId,
                target_user_id: targetUserId
            }
        });

        if (result) {
            if (relationType === dataConst.relationType.followed) {
                if (result.type === dataConst.relationType.followed) {
                    return false;
                } else if (result.type === dataConst.relationType.blacklist) {
                    result.type = dataConst.relationType.followed;
                    return await result.save();
                }
            } else if (relationType === dataConst.relationType.blacklist) {
                if (result.type === dataConst.relationType.blacklist) {
                    return false;
                } else if (result.type === dataConst.relationType.followed) {
                    result.type = dataConst.relationType.blacklist;
                    return await result.save();
                }
            }
        } else {
            return await app.model.MemberRelation.create({
                user_id: userId,
                target_user_id: targetUserId,
                type: relationType
            });
        }
    }

    async removeMemberRelationSrv(relationId) {
        const { app } = this;

        return app.model.MemberRelation.destroy({
            where: {
                id: relationId
            }
        });
    }

    async getMemberRelationListSrv({userId, relationType, limit, offset, orderName, orderType = 'asc'}) {
        const { ctx } = this;

        let queryString =
            `select
                mr.target_user_id as user_id
                , usr.username, usr.birthday, usr.nickname, usr.phone, usr.avatar
                , if(isnull(usr.avatar) || length(trim(usr.avatar)) < 1, null, concat(:serverUrl, :avatarPath, usr.id, '/', usr.avatar)) as avatar_url
                , ub.register_state, ub.body_height, ub.education_id, ub.month_pay, ub.live_region
                , ub.avatar_verify, ub.identity_verify, ub.education_verify, ub.online
                , timestampdiff(year, usr.birthday, curdate()) as age
                , lrg.latest_time
                from tbl_member_relation mr
                left join tbl_user usr
                on mr.target_user_id = usr.id
                left join tbl_user_base ub
                on usr.id = ub.user_id
                left join (
                select
                user_id, max(updated_at) as latest_time
                from tbl_login_record
                group by user_id) lrg
                on usr.id = lrg.user_id
                where true
                and usr.state = :state
                and usr.type = :type`;

        const paramsData = {
            serverUrl: dataConst.serverUrl,
            avatarPath: dataConst.avatarPath,
            state: dataConst.stateType.active,
            type: authConst.userType.user.value
        };

        if (userId) {
            queryString += ` and mr.user_id = :userId`;
            paramsData.userId = userId;
        }

        if (relationType) {
            queryString += ` and mr.type = :relationType`;
            paramsData.relationType = relationType;
        }

        if (orderName) {
            queryString += ` order by ${orderName} ${orderType}`;
        }

        return await ctx.helper.sqlQueryCount(queryString, paramsData, limit, offset);
    }

    async getMemberFollowedListSrv({targetUserId, relationType, limit, offset, orderName, orderType = 'asc'}) {
        const { ctx } = this;

        let queryString =
            `select
                mr.user_id
                , usr.username, usr.birthday, usr.nickname, usr.phone, usr.avatar
                , if(isnull(usr.avatar) || length(trim(usr.avatar)) < 1, null, concat(:serverUrl, :avatarPath, usr.id, '/', usr.avatar)) as avatar_url
                , ub.register_state, ub.body_height, ub.education_id, ub.month_pay, ub.live_region
                , ub.avatar_verify, ub.identity_verify, ub.education_verify, ub.online
                , timestampdiff(year, usr.birthday, curdate()) as age
                , lrg.latest_time
                from tbl_member_relation mr
                left join tbl_user usr
                on mr.user_id = usr.id
                left join tbl_user_base ub
                on usr.id = ub.user_id
                left join (
                select
                user_id, max(updated_at) as latest_time
                from tbl_login_record
                group by user_id) lrg
                on usr.id = lrg.user_id
                where true
                and usr.state = :state
                and usr.type = :type`;

        const paramsData = {
            serverUrl: dataConst.serverUrl,
            avatarPath: dataConst.avatarPath,
            state: dataConst.stateType.active,
            type: authConst.userType.user.value
        };

        if (targetUserId) {
            queryString += ` and mr.target_user_id = :targetUserId`;
            paramsData.targetUserId = targetUserId;
        }

        if (relationType) {
            queryString += ` and mr.type = :relationType`;
            paramsData.relationType = relationType;
        }

        if (orderName) {
            queryString += ` order by ${orderName} ${orderType}`;
        }

        return await ctx.helper.sqlQueryCount(queryString, paramsData, limit, offset);
    }

    async getMemberLookForListSrv(userId, limit, offset) {
        const { ctx } = this;

        let queryString =
            `select
                lrg.target_user_id as user_id, lrg.amount, lrg.latest_time, usr.nickname, usr.gender
                , if(isnull(usr.avatar) || length(trim(usr.avatar)) < 1, null, concat(:serverUrl, :avatarPath, usr.id, '/', usr.avatar)) as avatar_url
                , date(usr.birthday) as birthday
                , timestampdiff(year, usr.birthday, curdate()) as age
                , ub.register_state, ub.body_height, ub.education_id, ub.month_pay, ub.live_region
                , ub.avatar_verify, ub.identity_verify, ub.education_verify, ub.online
                from (
                select
                target_user_id, sum(amount) as amount, max(updated_at) as latest_time
                from tbl_look_record
                where user_id = :userId
                group by target_user_id) lrg
                left join tbl_user usr
                on lrg.target_user_id = usr.id
                left join tbl_user_base ub
                on usr.id = ub.user_id
                where true
                and usr.state = :state
                and usr.type = :type
                order by latest_time desc`;

        const paramsData = {
            serverUrl: dataConst.serverUrl,
            avatarPath: dataConst.avatarPath,
            userId,
            state: dataConst.stateType.active,
            type: authConst.userType.user.value
        };

        return await ctx.helper.sqlQueryCount(queryString, paramsData, limit, offset);
    }

    async getMemberLookMeListSrv(targetUserId, limit, offset) {
        const { ctx } = this;

        let queryString =
            `select
                lrg.user_id, lrg.amount, lrg.latest_time, usr.nickname, usr.gender
                , if(isnull(usr.avatar) || length(trim(usr.avatar)) < 1, null, concat(:serverUrl, :avatarPath, usr.id, '/', usr.avatar)) as avatar_url
                , date(usr.birthday) as birthday
                , timestampdiff(year, usr.birthday, curdate()) as age
                , ub.register_state, ub.body_height, ub.education_id, ub.month_pay, ub.live_region
                , ub.avatar_verify, ub.identity_verify, ub.education_verify, ub.online
                from (
                select
                user_id, sum(amount) as amount, max(updated_at) as latest_time
                from tbl_look_record
                where target_user_id = :targetUserId
                group by user_id) lrg
                left join tbl_user usr
                on lrg.user_id = usr.id
                left join tbl_user_base ub
                on usr.id = ub.user_id
                where true
                and usr.state = :state
                and usr.type = :type
                order by latest_time desc`;

        const paramsData = {
            serverUrl: dataConst.serverUrl,
            avatarPath: dataConst.avatarPath,
            targetUserId,
            state: dataConst.stateType.active,
            type: authConst.userType.user.value
        };

        return await ctx.helper.sqlQueryCount(queryString, paramsData, limit, offset);
    }

    async getMemberFollowedCount(userId) {
        const { ctx } = this;

        const queryString =
            `select
                count(*) as count
                from tbl_member_relation mr
                left join tbl_user usr
                on mr.user_id = usr.id
                where true
                and usr.state = :state
                and mr.target_user_id = :userId`;

        const paramsData = {
            state: dataConst.stateType.active,
            userId
        };

        const [result] = await ctx.helper.sqlQuery(queryString, paramsData);
        if (result) {
            const {count} = result;
            return count || 0;
        } else {
            return 0;
        }
    }
}

module.exports = MemberRelationService;
